package com.navi.loaddata.loadService.impl.mesData.glassSumData;


import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.util.LoggerUtil;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.text.SimpleDateFormat;
import java.util.Date;

@Service
public class LoadCPanelSumBean {
    private static final Logger logger = LoggerFactory.getLogger(LoadCPanelSumBean.class);

    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void InsertCPanelSumData(EtlConfDModel etlConfDModel, String errMsg, int errCnt) throws Exception {
        Session session = gpDao.getEntityManagerSession();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String startTime = sdf.format(etlConfDModel.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfDModel.getRunEndTimestamp().getTime());

        StringBuffer sqlInsr = new StringBuffer();

        sqlInsr.append("insert into wpp_cdefect_glass_sum (evt_timestamp,cald_date, jge_cnt, ope_id, prod_id, eqpt_id, owner, ownertype, glass_grade, glass_rej_code,  hour_key_fk, shf_id, shop_id, shop_key_fk, db_timestamp, panel_qty)");

        sqlInsr.append(" select case when cast(substring(logof_timestamp,15,2) as int4) >= 0");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 10");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'00:00' as timestamp)");
        sqlInsr.append(" when cast(substring(logof_timestamp,15,2) as int4) >= 10");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 20");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'10:00' as timestamp)");
        sqlInsr.append(" when cast(substring(logof_timestamp,15,2) as int4) >= 20");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 30");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'20:00' as timestamp)");
        sqlInsr.append(" when cast(substring(logof_timestamp,15,2) as int4) >= 30");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 40");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'30:00' as timestamp)");
        sqlInsr.append(" when cast(substring(logof_timestamp,15,2) as int4) >= 40");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 50");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'40:00' as timestamp)");
        sqlInsr.append(" else cast(substring(logof_timestamp,1,14)||'50:00' as timestamp) end evt_timestamp,");

        sqlInsr.append(" cald_date, rework_count as jge_cnt, ope_id, prod_id, eqpt_id, owner, ownertype, coalesce(grade, '-') as glass_grade, coalesce(defect_code, '-') as glass_rej_code,  hour_key_fk, shf_id, 'OC' as shop_id, 3 as shop_key_fk, now() as db_timestamp, count(1) as panel_qty");
        sqlInsr.append(" from wpp_csht_ope_his_n");
        sqlInsr.append(" where db_timestamp >= '" + startTime + "'" );
        sqlInsr.append(" and db_timestamp < '" + endTime + "'");
        sqlInsr.append(" and ope_id = '"+ etlConfDModel.getOpe() + "'");
        sqlInsr.append(" group by substring(logof_timestamp,15,2), substring(logof_timestamp,1,14), cald_date, rework_count, ope_id, prod_id, eqpt_id, owner, ownertype, grade, defect_code, hour_key_fk, shf_id");

        try {
            SQLQuery Insr_query = session.createSQLQuery(sqlInsr.toString());
            logger.info("Insert wpp_csht_ope_his_n " + etlConfDModel.getOpe() + " data SQL:" + sqlInsr.toString());

            Date startDate = new Date();

            Insr_query.executeUpdate();

            Date end = new Date();
            long durableTime = (end.getTime() - startDate.getTime())/1000;
            logger.info("Insert data from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
            logger.info("Insert data耗时:" + durableTime +"s");
        } catch (ETLException2 e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlInsr.toString():  **********    " + sqlInsr.toString());

            if (!org.apache.commons.lang.StringUtils.isEmpty(errMsg)) {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            } else {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            }

            errCnt++;

            e.printStackTrace();

            throw new RuntimeException(e);
        }
    }



    @Transactional(transactionManager = "secondaryPlatformTransactionManager",
            rollbackFor = {RuntimeException.class, Exception.class})
    public void DeleteCPanelSumData(EtlConfDModel etlConfDModel, String errMsg, int errCnt) throws Exception {
        Session session = gpDao.getEntityManagerSession();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String startTime = sdf.format(etlConfDModel.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfDModel.getRunEndTimestamp().getTime());

        StringBuffer sqlInsr = new StringBuffer();

        sqlInsr.append("delete from wpp_cdefect_glass_sum");
        sqlInsr.append(" where (evt_timestamp, ope_id, jge_cnt, glass_grade, glass_rej_code) in");
        sqlInsr.append(" (select distinct case when cast(substring(logof_timestamp,15,2) as int4) >= 0");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 10");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'00:00' as timestamp)");
        sqlInsr.append(" when cast(substring(logof_timestamp,15,2) as int4) >= 10");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 20");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'10:00' as timestamp)");
        sqlInsr.append(" when cast(substring(logof_timestamp,15,2) as int4) >= 20");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 30");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'20:00' as timestamp)");
        sqlInsr.append(" when cast(substring(logof_timestamp,15,2) as int4) >= 30");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 40");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'30:00' as timestamp)");
        sqlInsr.append(" when cast(substring(logof_timestamp,15,2) as int4) >= 40");
        sqlInsr.append(" and cast(substring(logof_timestamp,15,2) as int4) < 50");
        sqlInsr.append(" then cast(substring(logof_timestamp,1,14)||'40:00' as timestamp)");
        sqlInsr.append(" else cast(substring(logof_timestamp,1,14)||'50:00' as timestamp) end evt_timestamp,");
        sqlInsr.append(" ope_id, rework_count, grade, defect_code");
        sqlInsr.append(" from wpp_csht_ope_his_n");
        sqlInsr.append(" where db_timestamp >= '" + startTime + "'" );
        sqlInsr.append(" and db_timestamp < '" + endTime + "'");
        sqlInsr.append(" and ope_id = '"+ etlConfDModel.getOpe() + "'");
        sqlInsr.append(" group by cald_date, rework_count, ope_id, prod_id, eqpt_id, owner, ownertype, grade, defect_code, hour_key_fk, shf_id");

        try {
            SQLQuery Insr_query = session.createSQLQuery(sqlInsr.toString());
            logger.info("Delete wpp_csht_ope_his_n " + etlConfDModel.getOpe() + " data SQL:" + sqlInsr.toString());

            Date startDate = new Date();

            Insr_query.executeUpdate();

            Date end = new Date();
            long durableTime = (end.getTime() - startDate.getTime())/1000;
            logger.info("Delete data from " + etlConfDModel.getRunStartTimestamp() + " to " + etlConfDModel.getRunEndTimestamp() + " finish!", etlConfDModel.getOpe());
            logger.info("Delete data耗时:" + durableTime +"s");
        } catch (ETLException2 e) {
            logger.error(LoggerUtil.stackTraceToString(e));
            logger.error("************   sqlInsr.toString():  **********    " + sqlInsr.toString());

            if (!org.apache.commons.lang.StringUtils.isEmpty(errMsg)) {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            } else {
                errMsg = errMsg
                        + "转化失败,错误原因：" + e.getErrMsg() + "。\n";
            }

            errCnt++;

            e.printStackTrace();

            throw new RuntimeException(e);
        }
    }
}
